Imports MySql.Data.MySqlClient

''' <summary>
''' MySQL
''' </summary>
''' <remarks>
''' References: MySql.Data.dll
''' The default administrative login is root/(no default password)
''' </remarks>
Public Class clsDBMySQL
    Inherits clsDB

    ''' <summary>
    ''' Return a copy of the current generic connection
    ''' </summary>
    ''' <returns>Clone of current instance</returns>
    Public Overrides Function Clone() As clsDB
        Dim db As clsDB = New clsDBMySQL
        MyBase.CloneSelf(db)
        Return db
    End Function

    ''' <summary>
    ''' Provider-independent access to CommandBuilder object
    ''' </summary>
    ''' <param name="da">Data adapter</param>
    ''' <returns>CommandBuilder object specific to provider type</returns>
    Public Overrides Function CommandBuilder(ByVal da As DbDataAdapter) As DbCommandBuilder
        Return New MySqlCommandBuilder(da)
    End Function

    ''' <summary>
    ''' Return a data adapter for the current connection containing data from sql string
    ''' </summary>
    ''' <param name="SqlString">standard sql string</param>
    ''' <returns>Data Adapter</returns>
    Public Overrides Function DataAdapter(ByVal SqlString As String) As DbDataAdapter
        If Connection Is Nothing Then Return Nothing
        Return New MySqlDataAdapter(SqlString, CType(Connection, MySqlConnection))
    End Function

    ''' <summary>
    ''' Create a parameter (used to speed up queries and updates); value is initialized to dbnull
    ''' </summary>
    ''' <param name="ParameterName">name of parameter (usually field name)</param>
    ''' <param name="DataType">need to work on this to standarize</param>
    ''' <param name="Size">size if appropriate (strings)</param>
    ''' <returns>Data Parameter</returns>
    Public Overrides Function Parameter(ByRef Command As DbCommand, ByVal ParameterName As String, ByVal DataType As System.Type, Optional ByVal Size As Int16 = 0) As DbParameter
        If Connection Is Nothing Then Return Nothing
        Dim param As MySqlParameter
        Dim SqlType As MySqlDbType
        If DataType Is GetType(String) Then
            SqlType = MySqlDbType.VarChar
        ElseIf DataType Is GetType(Date) Then
            SqlType = MySqlDbType.DateTime
        ElseIf DataType Is GetType(Double) Then
            SqlType = MySqlDbType.Float
        ElseIf DataType Is GetType(Integer) Then
            SqlType = MySqlDbType.Int32
        ElseIf DataType Is GetType(Boolean) Then
            SqlType = MySqlDbType.Byte
        ElseIf DataType Is GetType(Short) Then
            SqlType = MySqlDbType.Int16
        Else
            Throw New ApplicationException("Invalid type passed to Parameter")
            Return Nothing
        End If
        'If Size > 0 Then
        '    param = New MySqlParameter(ParameterName, SqlType, Size)
        'Else
        '    param = New MySqlParameter(ParameterName, SqlType)
        'End If
        'param.Value = DBNull.Value
        param = Command.CreateParameter
        With param
            .ParameterName = ParameterName
            .MySqlDbType = SqlType
            If Size > 0 Then .Size = Size
            .Value = DBNull.Value
        End With
        Return param
    End Function

    ''' <summary>
    ''' Creates a provider-specific connection object given previously specified properties as required (DataSource, DatabaseName, UserName, Password, etc.)
    ''' Note that the connection is normally automatically created when you instantiate the class
    ''' </summary>
    Public Overrides Sub CreateConnection()
        Try
            Dim csb As New MySqlConnectionStringBuilder
            With csb
                .Server = DataSource
                .Database = DatabaseName
                .UserID = UserName
                .Password = Password
                .Pooling = False 'this was added when Icis web site started getting these errors.
                Connection = New MySqlConnection(.ConnectionString)
            End With
        Catch ex As Exception
            Throw
            Connection = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Creates new database on the provider (uses supervisor access)
    ''' </summary>
    ''' <param name="DatabaseName">Desired name of new database</param>
    ''' <returns>True if successful</returns>
    Public Overrides Function CreateDatabase(ByRef DatabaseName As String) As Boolean
        Dim csb As New MySqlConnectionStringBuilder
        With csb
            .Server = DataSource
            .Database = ""
            .UserID = UserName
            .Password = Password
            Dim cn As New MySqlConnection(.ConnectionString)
            With New MySqlCommand("CREATE DATABASE " & DatabaseName, cn)
                Try
                    cn.Open()
                    .ExecuteNonQuery()
                    Return True
                Catch ex As Exception
                    Throw New ApplicationException("Unable to create MySQL database: " & DatabaseName, ex)
                    Return False
                Finally
                    .Dispose()
                    cn.Close()
                    cn.Dispose()
                End Try
            End With
        End With
    End Function

    ''' <summary>
    ''' Return a list of columns for this table (all fields are converted to UPPERCASE)
    ''' </summary>
    ''' <param name="TableName">Name of table</param> 
    ''' <returns></returns>
    ''' <remarks>Allow for possibility that database name is prepended to tablename</remarks>
    Public Overrides Function GetColumnList(ByVal TableName As String) As Generic.List(Of String)
        Dim dr As DbDataReader = Nothing
        Try
            If Not OpenConnection() Then Return Nothing

            Dim col As New Generic.List(Of String)

            Dim dbName As String = DatabaseName
            Dim tblName As String = TableName
            If TableName.Contains(".") Then
                dbName = TableName.Split(".")(0)
                tblName = TableName.Split(".")(1)
            End If

            dr = ExecuteReader(String.Format("SELECT column_name FROM information_schema.columns WHERE table_name LIKE '{0}' AND table_schema LIKE '{1}'", tblName, dbName))
            While dr IsNot Nothing AndAlso dr.Read
                col.Add(dr.GetString(0).ToUpper)
            End While
            Return col
        Catch ex As Exception
            Return New Generic.List(Of String) 'return empty list to avoid later errors
        Finally
            If dr IsNot Nothing Then dr.Close()
            CloseConnection()
            'DbConnection.Close()
        End Try
    End Function

    ''' <summary>
    ''' Return list of indexed column for this table (all fields are converted to UPPERCASE)
    ''' </summary>
    ''' <param name="TableName">Name of table</param>
    ''' <returns>List of indexed fields (not including RecID) or Nothing if error occurred</returns>
    ''' <remarks>Allow for possibility that database name is prepended to tablename</remarks>
    Public Overrides Function GetIndexList(ByVal TableName As String) As Generic.List(Of String)
        Dim dr As DbDataReader = Nothing
        Try
            If Not OpenConnection() Then Return Nothing

            Dim lstIndex As New Generic.List(Of String)

            Dim dbName As String = DatabaseName
            Dim tblName As String = TableName
            If TableName.Contains(".") Then
                dbName = TableName.Split(".")(0)
                tblName = TableName.Split(".")(1)
            End If

            dr = ExecuteReader(String.Format("SELECT column_name FROM information_schema.statistics WHERE table_name LIKE '{0}' AND table_schema LIKE '{1}'", tblName, dbName))
            While dr IsNot Nothing AndAlso dr.Read
                lstIndex.Add(dr.GetString(0).ToUpper)
            End While
            Return lstIndex
        Catch ex As Exception
            Throw
            Return Nothing
        Finally
            If dr IsNot Nothing Then dr.Close()
            CloseConnection()
            'DbConnection.Close()
        End Try
    End Function

    ' following are not used because of timing problems; replace with new routine at bottom

    ''' <summary>
    ''' Return a list of tables or views for this project from both project database and temp database
    ''' </summary>
    ''' <returns>List of table or view names</returns>
    Private Function GetTable_ViewList(ByVal IsTable As Boolean) As Generic.List(Of String)
        Dim dr As DbDataReader = Nothing
        Try
            If Not OpenConnection() Then Return Nothing

            'this may be called frequently; cache the table/view list and refresh if this request occurred more than 1 second later
            Static LastRequest As DateTime = Now.AddDays(-1)
            Static lstTables, lstViews As New Generic.List(Of String)
            If Now.Subtract(LastRequest).TotalSeconds < 1 Then
                If IsTable Then Return lstTables Else Return lstViews
            End If
            lstTables.Clear()
            lstViews.Clear()
            For i As Integer = 1 To 2
                dr = ExecuteReader("SELECT table_name,table_type FROM information_schema.tables WHERE table_schema LIKE '{0}'", Choose(i, DatabaseName, TempDBName))
                While dr IsNot Nothing AndAlso dr.Read
                    Dim table_name As String = Proper(dr.GetString(0))
                    Dim table_type As String = dr.GetString(1)
                    If String.Compare(table_type, "Base Table", True) = 0 Then
                        lstTables.Add(table_name)
                    ElseIf String.Compare(table_type, "View", True) = 0 Then
                        lstViews.Add(table_name)
                    End If
                End While
                dr.Close()
            Next
            LastRequest = Now
            Return IIf(IsTable, lstTables, lstViews)
        Catch ex As Exception
            Throw
            Return Nothing
        Finally
            If dr IsNot Nothing Then dr.Close()
            CloseConnection()
        End Try
    End Function

    ''' <summary>
    ''' Return a list of  tables for this project
    ''' </summary>
    ''' <returns>List of table names</returns>
    Public Overrides Function GetTableList() As Generic.List(Of String)
        Return GetTable_ViewList(True)
    End Function

    ''' <summary>
    ''' Return a list of views for this project
    ''' </summary>
    ''' <returns>List of table names</returns>
    Public Overrides Function GetViewList() As Generic.List(Of String)
        Return GetTable_ViewList(False)
    End Function

    ''' <summary>
    ''' Determine the number of records in a table by starting a new thread
    ''' </summary>
    ''' <param name="TableName">Name of table</param>
    ''' <returns>Number of records (if timeout occurs, will return -1)</returns>
    ''' <param name="CommandTimeout">Number of seconds before timeout exception occurs (may be ignored by MySQL)</param>
    ''' <remarks>Must override for Paradox</remarks>
    Public Overrides Function GetRecordCount(ByVal TableName As String, Optional ByVal CommandTimeout As Integer = 10) As Integer
        Return ExecuteScalar("SELECT COUNT(*) FROM {0}", TableName)

        'information_schema for INNODB engine is only an approximation of the number of records, and doesn't accurately reflect deletions, etc.
        'If TableExists(TableName) Then
        '    Dim dbName As String = DatabaseName
        '    Dim tblName As String = TableName
        '    If TableName.Contains(".") Then
        '        dbName = TableName.Split(".")(0)
        '        tblName = TableName.Split(".")(1)
        '    End If
        '    Return ExecuteScalar("SELECT table_rows FROM information_schema.tables WHERE table_name LIKE '{0}' AND table_schema LIKE '{1}'", tblName, dbName)
        'ElseIf ViewExists(TableName) Then
        '    Return ExecuteScalar("SELECT COUNT(*) FROM {0}", TableName)
        'Else
        '    Return 0
        'End If
    End Function

    Private Function Table_ViewExists(ByVal Name As String, ByVal IsTable As Boolean) As Boolean
        Dim dbName As String = DatabaseName
        Dim tblName As String = Name
        If Name.Contains(".") Then
            dbName = Name.Split(".")(0)
            tblName = Name.Split(".")(1)
        End If
        Return (ExecuteScalar("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema LIKE '{0}' AND table_name LIKE '{1}' AND table_type LIKE '{2}'", dbName, tblName, IIf(IsTable, "Base Table", "View")) <> 0)
    End Function

    ''' <summary>
    ''' Determine if table exists in the database
    ''' </summary>
    ''' <param name="TableName">Name of table</param>
    ''' <returns>True if exists</returns>
    ''' <remarks>Override for Paradox</remarks>
    Public Overrides Function TableExists(ByVal TableName As String) As Boolean
        Return Table_ViewExists(TableName, True)
    End Function

    ''' <summary>
    ''' Determine if view exists in the database
    ''' </summary>
    ''' <param name="ViewName">Name of view</param>
    ''' <returns>True if exists</returns>
    Public Overrides Function ViewExists(ByVal ViewName As String) As Boolean
        Return Table_ViewExists(ViewName, False)
    End Function

End Class
